using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Text;
using System.Collections.Generic;
using eShop.Model;
using eShop.Utility;

namespace eShop.DataAccess
{
    public class EmailTemplateDAO : DatabaseUtility
    {
        public static Int32 CreateNewTblEmailTemplate
        (
            String emailTitle,
            String emailSubject,
            String emailBody,
            Boolean isActive,
            Boolean isDeleted
        )
        {
            //creating an empty TblEmailTemplate object
            EmailTemplate tblEmailTemplate = new EmailTemplate();
            tblEmailTemplate.EmailTitle = emailTitle;
            tblEmailTemplate.EmailSubject = emailSubject;
            tblEmailTemplate.EmailBody = emailBody;
            tblEmailTemplate.IsActive = isActive;
            tblEmailTemplate.IsDeleted = isDeleted;

            return EmailTemplateDAO.CreateNewTblEmailTemplate(tblEmailTemplate);
        }

        public static bool UpdateTblEmailTemplate
        (
            Int32 emailTemplateId,
            String emailTitle,
            String emailSubject,
            String emailBody,
            Boolean isActive,
            Boolean isDeleted
        )
        {
            EmailTemplate tblEmailTemplate = EmailTemplateDAO.GetTblEmailTemplateByEmailTemplateId(emailTemplateId);

            tblEmailTemplate.EmailTitle = emailTitle;
            tblEmailTemplate.EmailSubject = emailSubject;
            tblEmailTemplate.EmailBody = emailBody;
            tblEmailTemplate.IsActive = isActive;
            tblEmailTemplate.IsDeleted = isDeleted;

            return EmailTemplateDAO.UpdateTblEmailTemplate(tblEmailTemplate);
        }

        public static bool DeleteTblEmailTemplate
        (
            Int32 emailTemplateId
        )
        {
            //Validate Input
            if (emailTemplateId <= GetEmailTemplateIdMinValue)
                throw (new ArgumentOutOfRangeException("emailTemplateId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@EmailTemplateId", SqlDbType.Int, 0, ParameterDirection.Input, emailTemplateId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLEMAILTEMPLATE_DELETETBLEMAILTEMPLATE);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);

        }

        public static bool DeleteTblEmailTemplateList(System.Collections.Generic.List<Int32> emailTemplateIdListToDelete)
        {
            //Validate Input
            foreach (Int32 emailTemplateId in emailTemplateIdListToDelete)
                if (emailTemplateId <= GetEmailTemplateIdMinValue)
                    throw (new ArgumentOutOfRangeException("emailTemplateId"));

            //performing deletion operation //

            string xmlString = FormatXMLForIdArray(emailTemplateIdListToDelete);

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@EmailTemplateIdList", SqlDbType.Xml, xmlString.ToString().Length, ParameterDirection.Input, xmlString.ToString());
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLEMAILTEMPLATE_DELETETBLEMAILTEMPLATELIST);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == emailTemplateIdListToDelete.Count ? true : false);
        }

        public static EmailTemplate GetTblEmailTemplateByEmailTemplateId(Int32 emailTemplateId)
        {
            if (emailTemplateId <= GetEmailTemplateIdMinValue)
                throw (new ArgumentOutOfRangeException("emailTemplateId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@EmailTemplateId", SqlDbType.Int, 0, ParameterDirection.Input, emailTemplateId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLEMAILTEMPLATE_GETTBLEMAILTEMPLATEBYEMAILTEMPLATEID);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblEmailTemplateCollectionFromReader);
            List<EmailTemplate> objCollection = ((List<EmailTemplate>)ExecuteReaderCmd(sqlCmd, test));

            if (objCollection.Count > 0)
                return objCollection[0];
            else
                return null;
        }

        public static List<EmailTemplate> GetAllTblEmailTemplate()
        {

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLEMAILTEMPLATE_GETALLTBLEMAILTEMPLATE);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblEmailTemplateCollectionFromReader);
            List<EmailTemplate> objCollection = ((List<EmailTemplate>)ExecuteReaderCmd(sqlCmd, test));

            return objCollection;
        }

        public static List<EmailTemplate> GetAllTblEmailTemplatePaged(String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            //Validate Input
            if (orderBy == "")
                orderBy = "EmailTemplateId";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRowIndex);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, maximumRows);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLEMAILTEMPLATE_GETALLTBLEMAILTEMPLATEPAGED);

            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblEmailTemplateCollectionFromReader);
            List<EmailTemplate> objCollection = ((List<EmailTemplate>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetAllTblEmailTemplatePagedCount(String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLEMAILTEMPLATE_GETALLTBLEMAILTEMPLATEPAGEDCOUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static bool Save(EmailTemplate emailTemplate)
        {
            if (emailTemplate.EmailTemplateId <= GetEmailTemplateIdMinValue)
            {
                Int32 TempId = CreateNewTblEmailTemplate(emailTemplate);
                if (TempId > 0)
                {
                    emailTemplate.EmailTemplateId = TempId;
                    return true;
                }
                else
                {
                    // error occurs
                    return false;
                }
            }
            else
            {	//This is a update Command
                return (UpdateTblEmailTemplate(emailTemplate));
            }

        }

        public static Int32 CreateNewTblEmailTemplate(EmailTemplate newTblEmailTemplate)
        {
            // validate Parameters
            if (newTblEmailTemplate == null)
                throw (new ArgumentNullException("newTblEmailTemplate"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            int length = 0;
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParameterToSqlCmd(sqlCmd, "@EmailTitle", SqlDbType.NVarChar, 50, ParameterDirection.Input, newTblEmailTemplate.EmailTitle);
            AddParameterToSqlCmd(sqlCmd, "@EmailSubject", SqlDbType.NVarChar, 50, ParameterDirection.Input, newTblEmailTemplate.EmailSubject);
            AddParameterToSqlCmd(sqlCmd, "@EmailBody", SqlDbType.NVarChar, 4000, ParameterDirection.Input, newTblEmailTemplate.EmailBody);
            AddParameterToSqlCmd(sqlCmd, "@IsActive", SqlDbType.Bit, 0, ParameterDirection.Input, newTblEmailTemplate.IsActive);
            AddParameterToSqlCmd(sqlCmd, "@IsDeleted", SqlDbType.Bit, 0, ParameterDirection.Input, newTblEmailTemplate.IsDeleted);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLEMAILTEMPLATE_CREATENEWTBLEMAILTEMPLATE);
            ExecuteScalarCmd(sqlCmd);
            return (Int32.Parse(sqlCmd.Parameters["@ReturnValue"].Value.ToString()));

        }

        public static bool UpdateTblEmailTemplate(EmailTemplate newTblEmailTemplate)
        {
            // validate Parameters
            if (newTblEmailTemplate == null)
                throw (new ArgumentNullException("newTblEmailTemplate"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            int length = 0;
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParameterToSqlCmd(sqlCmd, "@EmailTemplateId", SqlDbType.Int, 0, ParameterDirection.Input, newTblEmailTemplate.EmailTemplateId);
            AddParameterToSqlCmd(sqlCmd, "@EmailTitle", SqlDbType.NVarChar, 50, ParameterDirection.Input, newTblEmailTemplate.EmailTitle);
            AddParameterToSqlCmd(sqlCmd, "@EmailSubject", SqlDbType.NVarChar, 50, ParameterDirection.Input, newTblEmailTemplate.EmailSubject);
            AddParameterToSqlCmd(sqlCmd, "@EmailBody", SqlDbType.NVarChar, 4000, ParameterDirection.Input, newTblEmailTemplate.EmailBody);
            AddParameterToSqlCmd(sqlCmd, "@IsActive", SqlDbType.Bit, 0, ParameterDirection.Input, newTblEmailTemplate.IsActive);
            AddParameterToSqlCmd(sqlCmd, "@IsDeleted", SqlDbType.Bit, 0, ParameterDirection.Input, newTblEmailTemplate.IsDeleted);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLEMAILTEMPLATE_UPDATETBLEMAILTEMPLATE);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);
        }

        public static IList GenerateTblEmailTemplateCollectionFromReader(IDataReader returnData)
        {
            //creating the instance of Employee collection
            List<EmailTemplate> colTblEmailTemplate = new List<EmailTemplate>();

            while (returnData.Read())
            {
                //passing the TblEmailTemplate constructor parameters from the current instance of data reader fields.
                EmailTemplate newTblEmailTemplate = new EmailTemplate()
                {
                    EmailTemplateId = returnData["EmailTemplateId"] == System.DBNull.Value ? GetEmailTemplateIdMinValue : (Int32)returnData["EmailTemplateId"],
                    EmailTitle = returnData["EmailTitle"] == System.DBNull.Value ? string.Empty : (String)returnData["EmailTitle"],
                    EmailSubject = returnData["EmailSubject"] == System.DBNull.Value ? string.Empty : (String)returnData["EmailSubject"],
                    EmailBody = returnData["EmailBody"] == System.DBNull.Value ? string.Empty : (String)returnData["EmailBody"],
                    IsActive = returnData["IsActive"] == System.DBNull.Value ? false : (Boolean)returnData["IsActive"],
                    IsDeleted = returnData["IsDeleted"] == System.DBNull.Value ? false : (Boolean)returnData["IsDeleted"]

                };
                //adding the Employee to the collection
                colTblEmailTemplate.Add(newTblEmailTemplate);
            }

            //returns the collection of Companys 
            return (colTblEmailTemplate);
        }

        public static Int32 GetEmailTemplateIdMinValue
        {
            get
            {
                return 0;
            }
        }

        //create method related contants
        internal const string SP_TBLEMAILTEMPLATE_CREATENEWTBLEMAILTEMPLATE = "sp_TblEmailTemplate_CreateNewTblEmailTemplate";
        internal const string SP_TBLEMAILTEMPLATE_UPDATETBLEMAILTEMPLATE = "sp_TblEmailTemplate_UpdateTblEmailTemplate";
        internal const string SP_TBLEMAILTEMPLATE_DELETETBLEMAILTEMPLATE = "sp_TblEmailTemplate_DeleteTblEmailTemplate";
        internal const string SP_TBLEMAILTEMPLATE_DELETETBLEMAILTEMPLATELIST = "sp_TblEmailTemplate_DeleteTblEmailTemplateList";
        internal const string SP_TBLEMAILTEMPLATE_GETTBLEMAILTEMPLATEBYEMAILTEMPLATEID = "sp_TblEmailTemplate_GetTblEmailTemplateByEmailTemplateId";
        internal const string SP_TBLEMAILTEMPLATE_GETALLTBLEMAILTEMPLATE = "sp_TblEmailTemplate_GetAllTblEmailTemplate";
        internal const string SP_TBLEMAILTEMPLATE_GETALLTBLEMAILTEMPLATEPAGED = "sp_TblEmailTemplate_GetAllTblEmailTemplatePaged";
        internal const string SP_TBLEMAILTEMPLATE_GETALLTBLEMAILTEMPLATEPAGEDCOUNT = "sp_TblEmailTemplate_GetAllTblEmailTemplatePagedCount";

        public static List<EmailTemplate> GetEmailTemplateByTitle(string emailTitle)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@EmailTitle", SqlDbType.NVarChar, 50, ParameterDirection.Input, emailTitle);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_EMAILTEMPLATE_GET_ACTIVE_EMAILTEMPLATE_BY_EMAIL_TITLE);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblEmailTemplateCollectionFromReader);
            List<EmailTemplate> objCollection = ((List<EmailTemplate>)ExecuteReaderCmd(sqlCmd, reader));
            return objCollection;
        }

        private const string SP_TBL_EMAILTEMPLATE_GET_ACTIVE_EMAILTEMPLATE_BY_EMAIL_TITLE = "sp_TblEmailTemplate_Get_Active_EmailTemplate_By_EmailTitle";
    }
}
